use super::database::{DataBase, MyRow, ExecuteResult};
use super::env::TOKIO;
use super::value_helper::mssql2lua;
use deadpool_tiberius::{self, Pool, tiberius};
use mlua::UserData;
use anyhow::Result;

impl MyRow for tiberius::Row{}

pub(crate) struct MsSql{
    pool: Option<Pool>
}

impl MsSql{
    pub fn default() -> Self{
        Self{pool: None}
    }
}

impl DataBase for MsSql{
    type Row = tiberius::Row;
    async fn connect(&mut self, host: Option<String>, port: Option<u16>, user: Option<String>, password: Option<String>, database: String, timeout: Option<u64>) ->  Result<()> {
        let pool = deadpool_tiberius::Manager::new()
            .host(host.unwrap_or("localhost".into()))
            .port(port.unwrap_or(1433))
            .database(database)
            .basic_authentication(user.unwrap_or("sa".into()), password.unwrap_or("password".into()))
            .trust_cert()
            .wait_timeout(tokio::time::Duration::from_secs(timeout.unwrap_or(10)))
            .create_pool()?;
        self.pool = Some(pool);
        Ok(())
    }
    async fn execute(&self, sql: String) -> Result<ExecuteResult> {
        if let Some(pool) = &self.pool{
            let mut conn = pool.get().await?;
            let res = conn.execute(sql, &[]).await?;
            //TODO: last_insert_id
            Ok(ExecuteResult { rows_affected: res.rows_affected()[0], last_insert_id: None })
        }else{
            Err(anyhow::anyhow!("Database not connected"))
        }
    }
    async fn query(&self, sql: String) -> Result<Vec<Self::Row>> {
        if let Some(pool) = &self.pool{
            let mut conn = pool.get().await?;
            let res = conn.query(sql, &[])
                .await?
                .into_first_result()
                .await?;
            Ok(res)
        }else{
            Err(anyhow::anyhow!("Database not connected"))
        }
    }
}

impl UserData for MsSql{
    fn add_methods<M: mlua::UserDataMethods<Self>>(methods: &mut M) {
        methods.add_async_method_mut(
            "connect", 
            |_lua, 
                    mut this,
                    (host, port, user, password, database, timeout)
                    :(Option<String>, Option<u16>, Option<String>, Option<String>, String, Option<u64>)| async move{
            let _guard = TOKIO.enter();
            println!("Connecting to MS SQL Server...");
            this.connect(host, port, user, password, database, timeout).await.map_err(|e| mlua::Error::external(e))?;
            println!("Connected to MS SQL Server");
            Ok(())
        });

        methods.add_async_method("execute", |lua, this, sql:String| async move{
            let _guard = TOKIO.enter();
            let res = this.execute(sql).await.map_err(|e| mlua::Error::external(e))?;
            let t = lua.create_table()?;
            t.set("rows_affected", res.rows_affected)?;
            t.set("last_insert_id", res.last_insert_id)?;
            Ok(t)
        });

        methods.add_async_method("query", |lua, this, sql: String| async move{
            let _gurad = TOKIO.enter();
            let rows = this.query(sql).await.map_err(|e| mlua::Error::external(e))?;
            let rows_table = lua.create_table()?;
            for(index, row) in rows.iter().enumerate(){
                let row_table = lua.create_table()?;
                for(i, col) in row.columns().iter().enumerate(){
                    let col_name = col.name();
                    let value = mssql2lua(&lua, row, i, &col.column_type())
                        .map_err(|e| mlua::Error::external(e))?;
                    row_table.set(col_name, value)?;
                }
                rows_table.set(index+1, row_table)?;
            }
            Ok(rows_table)
        });
    }
}